package com.fly.core;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutionException;

import org.apache.commons.lang3.StringUtils;
import org.apache.tomcat.jdbc.pool.DataSource;

import com.fly.utils.DataSourceUtils;

import lombok.extern.slf4j.Slf4j;

/**
 * 
 * 带事务支持的数据库操作模板类
 * 
 * @author 00fly
 * @version [版本号, 2016-3-5]
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
@Slf4j
public class MyJdbcTemplate
{
    // 使用ThreadLocal存储当前线程中的Connection对象
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
    
    private static DataSource dataSource = DataSourceUtils.initBy("jdbc-mysql");
    
    /**
     * 获取数据库连接
     * 
     * @return
     * @throws ExecutionException
     * @throws InterruptedException
     * @see [类、类#方法、类#成员]
     */
    protected Connection getConnection()
    {
        Connection connection = threadLocal.get();
        try
        {
            if (connection == null)
            {
                // 把 connection绑定到当前线程上
                connection = dataSource.getConnection();
                threadLocal.set(connection);
            }
            log.info("★★★★申请资源: {}", connection);
        }
        catch (Exception e)
        {
            log.error(e.getMessage(), e);
        }
        return connection;
    }
    
    /**
     * 带可变参数查询,返回执行结果
     * 
     * @param sql 查询sql
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> querySql(String sql, Object... para)
        throws SQLException
    {
        log.info("querySql: {}, para: {}", sql, para);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try
        {
            List<Map<String, Object>> list = new ArrayList<>();
            conn = getConnection();
            if (conn != null)
            {
                ps = conn.prepareStatement(sql);
                if (para != null && para.length > 0)
                {
                    for (int i = 0; i < para.length; i++)
                    {
                        ps.setObject(i + 1, para[i]);
                    }
                }
                rs = ps.executeQuery();
                ResultSetMetaData md = rs.getMetaData();
                int columnCount = md.getColumnCount(); // Map rowData
                while (rs.next())
                {
                    Map<String, Object> rowData = new LinkedHashMap<>();
                    for (int i = 1; i <= columnCount; i++)
                    {
                        rowData.put(md.getColumnName(i).toLowerCase(), rs.getObject(i));
                    }
                    list.add(rowData);
                }
            }
            return list;
        }
        finally
        {
            close(rs);
            close(ps);
            if (conn != null && conn.getAutoCommit())
            {
                freeConnection();
            }
            log.info("querySql end ");
        }
    }
    
    /**
     * 带可变参数条件的分页查询
     * 
     * @param sql 查询sql
     * @param pageNo 页号
     * @param pageSize 每页记录数
     * @param para 可变参数
     * @return
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public PaginationSupport queryForPagination(String sql, int pageNo, int pageSize, Object... para)
        throws SQLException
    {
        // 保证正整数
        pageNo = Math.max(pageNo, 1);
        pageSize = Math.max(pageSize, 1);
        // 查询记录总条数
        int index = sql.toLowerCase().indexOf(" from ");
        String countSql = "select count(1)" + StringUtils.substring(sql, index);
        long total = queryForLong(countSql, para);
        // 查询当前页数据
        StringBuilder sbSql = new StringBuilder(sql).append(" limit ").append(pageSize * (pageNo - 1)).append(", ").append(pageSize);
        List<Map<String, Object>> list = querySql(sbSql.toString(), para);
        // 封装返回分页对象
        PaginationSupport page = new PaginationSupport(total, pageNo, pageSize);
        page.setItems(list);
        return page;
    }
    
    /**
     * 带可变参数查询，返回记录条数
     * 
     * @param countSql 查询记录条数的sql
     * @param para 可变参数
     * @return
     * @throws SQLException
     * @throws SQLException
     */
    public Long queryCountSql(String countSql, Object... para)
        throws SQLException
    {
        log.info("queryCountSql: {}, para: {}", countSql, para);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try
        {
            Long count = null;
            conn = getConnection();
            if (conn != null)
            {
                ps = conn.prepareStatement(countSql);
                if (null != para && para.length > 0)
                {
                    for (int i = 0; i < para.length; i++)
                    {
                        ps.setObject(i + 1, para[i]);
                    }
                }
                rs = ps.executeQuery();
                while (rs.next())
                {
                    count = rs.getLong(1);
                }
            }
            return count;
        }
        finally
        {
            close(rs);
            close(ps);
            if (conn != null && conn.getAutoCommit())
            {
                freeConnection();
            }
            log.info("queryCountSql end ");
        }
    }
    
    /**
     * 带可变参数查询，返回long类型数据
     * 
     * @param sql 查询sql
     * @param para 可变参数
     * @return
     * @throws SQLException
     * @throws SQLException
     */
    public Long queryForLong(String sql, Object... para)
        throws SQLException
    {
        return queryCountSql(sql, para);
    }
    
    /**
     * 带可变参数查询,返回首条执行结果
     * 
     * @param sql 查询sql
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public Map<String, Object> queryFirst(String sql, Object... para)
        throws SQLException
    {
        if (!sql.contains(" limit ")) // 前后有空格
        {
            sql = sql + " limit 1";
        }
        List<Map<String, Object>> list = querySql(sql, para);
        if (list.isEmpty())
        {
            return Collections.emptyMap();
        }
        return list.get(0);
    }
    
    /**
     * 带可变参数, 执行sql插入，返回新增记录的自增主键<BR>
     * 注意： 若插入的表无自增主键则返回 0，异常的话则返回 null
     * 
     * @param sql
     * @param para
     * @return
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    @SuppressWarnings("resource")
    public Long insertSql(String sql, Object[] para)
        throws SQLException
    {
        log.info("InsertSql: {}, para: {}", sql, para);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try
        {
            Long id = null;
            conn = getConnection();
            if (conn != null)
            {
                // step1: 执行插入操作
                ps = conn.prepareStatement(sql);
                if (null != para && para.length > 0)
                {
                    for (int i = 0; i < para.length; i++)
                    {
                        ps.setObject(i + 1, para[i]);
                    }
                }
                ps.executeUpdate();
                // step2: 查询新增记录的自增主键
                ps = conn.prepareStatement("SELECT @@IDENTITY");
                rs = ps.executeQuery();
                while (rs.next())
                {
                    id = rs.getLong(1);
                }
            }
            return id;
        }
        finally
        {
            close(rs);
            close(ps);
            if (conn != null && conn.getAutoCommit())
            {
                freeConnection();
            }
            log.info("InsertSql end ");
        }
    }
    
    /**
     * 带可变参数, 执行sql，返回执行结果
     * 
     * @param sql 执行的sql 语句
     * @param para 可变参数
     * @return
     * @throws SQLException
     */
    public int execSql(String sql, Object... para)
        throws SQLException
    {
        log.info("execSql: {}, para: {}", sql, para);
        Connection conn = null;
        PreparedStatement ps = null;
        try
        {
            int count = 0;
            conn = getConnection();
            if (conn != null)
            {
                ps = conn.prepareStatement(sql);
                if (null != para && para.length > 0)
                {
                    for (int i = 0; i < para.length; i++)
                    {
                        ps.setObject(i + 1, para[i]);
                    }
                }
                count = ps.executeUpdate();
            }
            return count;
        }
        finally
        {
            close(ps);
            if (conn != null && conn.getAutoCommit())
            {
                freeConnection();
            }
            log.info("execSql end ");
        }
    }
    
    /**
     * 批量更新
     * 
     * @param sql 需执行的sql
     * @param params 二维参数数组
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public void executeBatch(String sql, Object[][] params)
        throws SQLException
    {
        log.info("executeBatch: {}, params:{}", sql, params);
        Connection conn = null;
        PreparedStatement ps = null;
        try
        {
            conn = getConnection();
            if (conn != null)
            {
                ps = conn.prepareStatement(sql);
                if (null != params && params.length > 0)
                {
                    for (Object[] para : params)
                    {
                        for (int i = 0; i < para.length; i++)
                        {
                            ps.setObject(i + 1, para[i]);
                        }
                        ps.addBatch();
                    }
                }
                ps.executeBatch();
            }
        }
        finally
        {
            close(ps);
            if (conn != null && conn.getAutoCommit())
            {
                freeConnection();
            }
            log.info("executeBatch end ");
        }
    }
    
    /**
     * 批量更新
     * 
     * @param sql 需执行的sql
     * @param params List参数组
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public void executeBatch(String sql, List<Object[]> params)
        throws SQLException
    {
        log.info("executeBatch: {}, params:{}", sql, params);
        Connection conn = null;
        PreparedStatement ps = null;
        try
        {
            conn = getConnection();
            if (conn != null)
            {
                ps = conn.prepareStatement(sql);
                if (null != params && !params.isEmpty())
                {
                    for (Object[] para : params)
                    {
                        for (int i = 0; i < para.length; i++)
                        {
                            ps.setObject(i + 1, para[i]);
                        }
                        ps.addBatch();
                    }
                }
                ps.executeBatch();
            }
        }
        finally
        {
            close(ps);
            if (conn != null && conn.getAutoCommit())
            {
                freeConnection();
            }
            log.info("executeBatch end ");
        }
    }
    
    /**
     * 释放数据库连接
     * 
     * @see [类、类#方法、类#成员]
     */
    public void freeConnection()
    {
        try
        {
            Connection conn = threadLocal.get();
            if (conn != null)
            {
                threadLocal.remove(); // 解除当前线程上绑定conn
                conn.setAutoCommit(true);
                conn.close();
            }
            log.info("◆◆◆◆释放资源: {}", conn);
        }
        catch (Exception e)
        {
            log.error(e.getMessage(), e);
        }
    }
    
    /**
     * 关闭 ResultSet
     * 
     * @param rs
     * @see [类、类#方法、类#成员]
     */
    public static void close(ResultSet rs)
    {
        try
        {
            if (rs != null)
            {
                rs.close();
            }
        }
        catch (SQLException e)
        {
            log.error(e.getMessage(), e);
        }
    }
    
    /**
     * 关闭 PreparedStatement
     * 
     * @param ps
     * @see [类、类#方法、类#成员]
     */
    public static void close(PreparedStatement ps)
    {
        try
        {
            if (ps != null)
            {
                ps.close();
            }
        }
        catch (SQLException e)
        {
            log.error(e.getMessage(), e);
        }
    }
    
    /**
     * 关闭 Statement
     * 
     * @param ps
     * @see [类、类#方法、类#成员]
     */
    public static void close(Statement stmt)
    {
        try
        {
            if (stmt != null)
            {
                stmt.close();
            }
        }
        catch (SQLException e)
        {
            log.error(e.getMessage(), e);
        }
    }
    
    // ************** 事务操作 **************
    /**
     * 开启事务
     * 
     * @param connection
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    public void startTransaction()
    {
        log.info("------开启事务-------");
        try
        {
            Connection conn = threadLocal.get();
            if (conn == null)
            {
                conn = getConnection();
                threadLocal.set(conn);
            }
            conn.setAutoCommit(false); // 开启事务
        }
        catch (Exception e)
        {
            log.error(e.getMessage(), e);
        }
    }
    
    /**
     * 提交事务并关闭连接
     * 
     * @see [类、类#方法、类#成员]
     */
    public void commitAndClose()
    {
        log.info("------提交事务-------");
        try
        {
            Connection conn = threadLocal.get();
            if (conn != null)
            {
                conn.commit();
            }
        }
        catch (Exception e)
        {
            log.error(e.getMessage(), e);
        }
        finally
        {
            freeConnection();
        }
    }
    
    /**
     * 回滚事务并关闭连接
     * 
     * @see [类、类#方法、类#成员]
     */
    public void rollbackAndClose()
    {
        log.info("------ 系统异常，回滚事务-------");
        try
        {
            Connection conn = threadLocal.get();
            if (conn != null)
            {
                conn.rollback();
            }
        }
        catch (Exception e)
        {
            log.error(e.getMessage(), e);
        }
        finally
        {
            freeConnection();
        }
    }
}
